package com.mbc.hr.manager;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.persistence.NoResultException;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureQuery;

import org.json.JSONArray;
import org.json.JSONObject;

import com.mbc.common.CommonSingleton;
import com.mbc.common.CommonConstants.RecordStatus;
import com.mbc.common.entities.SystemProperty;
import com.mbc.common.exception.DataNotFoundException;
import com.mbc.common.manager.AbstractMgr;
import com.mbc.common.utils.EmailUtil;
import com.mbc.common.utils.KeyGenerator;
import com.mbc.hr.data.HRContants;
import com.mbc.hr.data.LeaveApprovedData;
import com.mbc.hr.data.LeaveTableData;
import com.mbc.hr.data.LeaveData;
import com.mbc.hr.data.LeaveDataList;
import com.mbc.hr.entities.ApprovedLeave;
import com.mbc.hr.entities.CalendarEvent;
import com.mbc.hr.entities.CalendarEventParticipant;
import com.mbc.hr.entities.Employee;
import com.mbc.hr.entities.EmployeeAttendancePenalty;
import com.mbc.hr.entities.EmployeeGroup;
import com.mbc.hr.entities.Leave;
import com.mbc.hr.entities.LeaveBalance;
import com.mbc.hr.entities.LeaveSchemeDetail;
import com.mbc.hr.entities.LeaveType;
import com.mbc.hr.exception.ExistingLeaveException;
import com.mbc.hr.exception.NotEmployeeException;
import com.mbc.hr.exception.NotEnoughLeaveBalanceException;
import com.mbc.hr.param.LeaveCriteria;
import com.mbc.hr.param.LeaveTableCriteria;

public class LeaveMgr extends AbstractMgr {
	
	public LeaveMgr(){}
	
	public LeaveMgr(String sessionId){
		this.sessionId = sessionId;
	}
	
	public boolean isLeaveAvailableOnCalendar(String loginId)throws Exception{
		openConnection();
		try {
			Employee l_emp = null;
			try {
				l_emp = entityManager.createNamedQuery("Employee.findByEmployeeNo", Employee.class)
						.setParameter("employeeNo", loginId)
						.getSingleResult();
			} catch (NoResultException e) {
				return false;
			}
			EmployeeGroup l_empGroup = entityManager.find(EmployeeGroup.class, l_emp.getEmployeeGroupID());
			
			if(l_empGroup.getLeaveSchemeID() == null)
				return false;
			SystemProperty l_sysProp = entityManager.find(SystemProperty.class, "calendar_leave");
			if(l_sysProp == null)
				return false;
			List<LeaveSchemeDetail> l_schemeDetails = entityManager.createNamedQuery("LeaveSchemeDetail.findAllByLeaveScheme", LeaveSchemeDetail.class)
					.setParameter("leaveSchemeID", l_empGroup.getLeaveSchemeID())
					.getResultList();
			boolean l_exist = false;
			for (LeaveSchemeDetail l_detail : l_schemeDetails) {
				if(l_detail.getLeaveTypeID() == Long.parseLong(l_sysProp.getValue())){
					l_exist = true;
					break;
				}
			}
			return l_exist;
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public void applyLeave(Leave leave, String loginId)throws Exception{
		openConnection();
		try {
			beginTransaction();
			
			// get EmpID 
			Employee l_emp = null;
			try {
				l_emp =	entityManager.createNamedQuery("Employee.findByEmployeeNo", Employee.class)
						.setParameter("employeeNo", loginId)
						.getSingleResult();
			} catch (NoResultException innerEx) {
				throw new NotEmployeeException("This login[" + loginId + "] user is not an employee.");
			}
			
			// get EmpGrp with EmpID to get TotalDay from AttendancePolicyID
			EmployeeGroup l_empGroup = entityManager.find(EmployeeGroup.class, l_emp.getEmployeeGroupID());
			
			leave.setCreatedDate(new Date());
			leave.setEmployeeID(l_emp.getEmployeeID());
			leave.setEmployeeName(l_emp.getName());
			if(leave.getLeavePeriodType() == HRContants.LeavePeriodType.FULL)
				leave.setTotalDays(CalendarEventMgr.getTotalDays(leave.getStartDateTime(), leave.getEndDateTime(), l_empGroup.getAttendancePolicyID(), entityManager));
			else
				leave.setTotalDays(0.5);
			
			LeaveType l_leaveType = entityManager.find(LeaveType.class, leave.getLeaveTypeID());
			leave.setLeaveDescription(l_leaveType.getName());
			
			Calendar l_cal = Calendar.getInstance();
			l_cal.setTime(leave.getStartDateTime());
			LeaveBalance l_leaveBalance = null;
			try {
				l_leaveBalance = entityManager.createNamedQuery("LeaveBalance.findByEmployeeIdLeaveTypeYear", LeaveBalance.class)
					.setParameter("employeeID", leave.getEmployeeID())
					.setParameter("leaveTypeID", leave.getLeaveTypeID())
					.setParameter("year", l_cal.get(Calendar.YEAR))
					.getSingleResult();
			} catch (NoResultException ex) {
				List<LeaveSchemeDetail> l_details = entityManager.createNamedQuery("LeaveSchemeDetail.findAllByLeaveScheme", LeaveSchemeDetail.class)
						.setParameter("leaveSchemeID", l_empGroup.getLeaveSchemeID())
						.getResultList();
				Calendar l_lsCalendar = Calendar.getInstance();
				l_lsCalendar.setTime(leave.getStartDateTime());
				int l_year = l_lsCalendar.get(Calendar.YEAR);
				for (LeaveSchemeDetail l_detail : l_details) {
					LeaveBalance l_balance = new LeaveBalance();
					l_balance.setEmployeeID(l_emp.getEmployeeID());
					l_balance.setLeaveTypeID(l_detail.getLeaveTypeID());
					l_balance.setYear(l_year);
					l_balance.setMaximum(l_detail.getDaysPerYear());
					l_balance.setTotal(0);
					entityManager.persist(l_balance);
					
					if(l_balance.getLeaveTypeID() == leave.getLeaveTypeID())
						l_leaveBalance = l_balance;
				}
				entityManager.flush();
			}
			if((l_leaveBalance.getMaximum() - l_leaveBalance.getTotal()) < leave.getTotalDays()){
				new NotEnoughLeaveBalanceException("Not enought leaves.");
			}
			
			LeaveSchemeDetail l_leaveSchemeDetail = entityManager.createNamedQuery("LeaveSchemeDetail.findByLeaveSchemeLeaveType", LeaveSchemeDetail.class)
					.setParameter("leaveSchemeID", l_empGroup.getLeaveSchemeID())
					.setParameter("leaveTypeID", leave.getLeaveTypeID())
					.getSingleResult();
			if(l_leaveSchemeDetail.getDaysPerMonth() != 0){
				Date l_start = new Date();
				Date l_end = new Date();
				CalendarEventMgr.getMonthStartNEnd(leave.getStartDateTime(), l_start, l_end);
				double l_count = entityManager.createNamedQuery("ApprovedLeave.getTotalLeavePerMonth", Double.class)
						.setParameter("employeeID", l_emp.getEmployeeID())
						.setParameter("startDateTime", l_start)
						.setParameter("endDateTime", l_end)
						.setParameter("leaveTypeID", leave.getLeaveTypeID())
						.setParameter("status", HRContants.LeaveStatus.APPROVED)
						.getSingleResult();
				if(l_count >= l_leaveSchemeDetail.getDaysPerMonth())
					throw new NotEnoughLeaveBalanceException("Not enought leaves for this month.", NotEnoughLeaveBalanceException.MONTH);
			}
			
			List<Leave> l_pendingLeaves = entityManager.createNamedQuery("Leave.findOverlapped", Leave.class)
					.setParameter("employeeID", leave.getEmployeeID())
					.setParameter("startDateTime", leave.getStartDateTime())
					.setParameter("endDateTime", leave.getEndDateTime())
					.getResultList();
			if(l_pendingLeaves.size() > 0)
				throw new ExistingLeaveException("There is a pending leave on " + leave.getStartDateTime() + ".");
			
			List<ApprovedLeave> l_ApprovedLeaves = entityManager.createNamedQuery("ApprovedLeave.findOverlapped", ApprovedLeave.class)
					.setParameter("employeeID", leave.getEmployeeID())
					.setParameter("startDateTime", leave.getStartDateTime())
					.setParameter("endDateTime", leave.getEndDateTime())
					.setParameter("status", HRContants.LeaveStatus.APPROVED)
					.getResultList();
			if(l_ApprovedLeaves.size() > 0)
				throw new ExistingLeaveException("There is a approved leave on " + leave.getStartDateTime() + ".");
			
			CalendarEvent l_calendarEvent = new CalendarEvent();
			l_calendarEvent.setEventID(KeyGenerator.generateKey(entityManager));
			l_calendarEvent.setCreatedBy(leave.getCreatedBy());
			l_calendarEvent.setDescription(leave.getReason());
			if((leave.getStartDateTime().getTime() + 3600000) < leave.getEndDateTime().getTime()){
				Calendar l_calendar = Calendar.getInstance();
				l_calendar.setTime(leave.getEndDateTime());
				l_calendar.add(Calendar.DAY_OF_MONTH, 1);
				
				l_calendarEvent.setEndDateTime(l_calendar.getTime());
			}
			l_calendarEvent.setEventName(leave.getReason());
			l_calendarEvent.setStartDateTime(leave.getStartDateTime());
			l_calendarEvent.setEventType((byte)4);
			
			entityManager.persist(l_calendarEvent);
			
			CalendarEventParticipant l_participant = new CalendarEventParticipant();
			l_participant.setEditable(true);
			l_participant.setEventID(l_calendarEvent.getEventID());
			l_participant.setParticipant(l_emp.getName());
			l_participant.setUserId(leave.getCreatedBy());
			
			entityManager.persist(l_participant);
			
			leave.setLeaveID(KeyGenerator.generateKey(entityManager));
			leave.setEventID(l_calendarEvent.getEventID());
			if(leave.getLeaveTypeID() == 0){
				SystemProperty l_sysProp = entityManager.find(SystemProperty.class, "calendar_leave");
				if(l_sysProp == null)
					throw new DataNotFoundException("No default calendar leave.");
				leave.setLeaveTypeID(Long.parseLong(l_sysProp.getValue()));
				
			}
			
			entityManager.persist(leave);
			
			Employee l_supervisor = entityManager.find(Employee.class, l_emp.getSupervisor());
			if(l_supervisor != null && !l_supervisor.getEmail().equals("")){
				SystemProperty l_userProp = entityManager.find(SystemProperty.class, "mail_user");
				SystemProperty l_pwdProp = entityManager.find(SystemProperty.class, "mail_password");
				if(l_userProp != null && l_pwdProp != null){
					try {
						LeaveType l_LeaveType = entityManager.find(LeaveType.class, leave.getLeaveTypeID());
						String l_fileName = CommonSingleton.ABSOLUTE_PATH + "data\\email_templates\\apply_leave.html";
						SimpleDateFormat l_dateTimeFormat = new SimpleDateFormat("hh:mm a d MMM yyyy");
						HashMap<String, String> l_parameters = new HashMap<String, String>();
						l_parameters.put("{name}", l_supervisor.getName());
						l_parameters.put("{applied_name}", l_emp.getName());
						l_parameters.put("{leave_type}", l_LeaveType.getName());
						l_parameters.put("{event}", leave.getReason());
						l_parameters.put("{start}", l_dateTimeFormat.format(leave.getStartDateTime()));
						l_parameters.put("{end}", l_dateTimeFormat.format(leave.getEndDateTime()));
						EmailUtil.sendWithHTMLTemplate(l_supervisor.getEmail(), "Leave Approval for the event[" + leave.getReason() + "]", l_userProp.getValue(), l_pwdProp.getValue(), l_fileName, l_parameters);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
			
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public void updateLeave(Leave leave)throws Exception{
		openConnection();
		try {
			beginTransaction();
			Employee l_emp = entityManager.find(Employee.class, leave.getEmployeeID());
			EmployeeGroup l_empGroup = entityManager.find(EmployeeGroup.class, l_emp.getEmployeeGroupID());
			if(leave.getLeavePeriodType() == HRContants.LeavePeriodType.FULL)
				leave.setTotalDays(CalendarEventMgr.getTotalDays(leave.getStartDateTime(), leave.getEndDateTime(), l_empGroup.getAttendancePolicyID(), entityManager));
			else
				leave.setTotalDays(.5);
			
			Calendar l_cal = Calendar.getInstance();
			l_cal.setTime(leave.getStartDateTime());
			LeaveBalance l_leaveBalance = entityManager.createNamedQuery("LeaveBalance.findByEmployeeIdLeaveTypeYear", LeaveBalance.class)
					.setParameter("employeeID", leave.getEmployeeID())
					.setParameter("leaveTypeID", leave.getLeaveTypeID())
					.setParameter("year", l_cal.get(Calendar.YEAR))
					.getSingleResult(); 
			if((l_leaveBalance.getMaximum() - l_leaveBalance.getTotal()) < leave.getTotalDays()){
				throw new NotEnoughLeaveBalanceException("Not enought leaves.");
			}
			
			LeaveSchemeDetail l_leaveSchemeDetail = entityManager.createNamedQuery("LeaveSchemeDetail.findByLeaveSchemeLeaveType", LeaveSchemeDetail.class)
					.setParameter("leaveSchemeID", l_empGroup.getLeaveSchemeID())
					.setParameter("leaveTypeID", leave.getLeaveTypeID())
					.getSingleResult();
			LeaveType l_leaveType = entityManager.find(LeaveType.class, leave.getLeaveTypeID());
			leave.setLeaveDescription(l_leaveType.getName());
			if(l_leaveSchemeDetail.getDaysPerMonth() != 0){
				Date l_start = null;
				Date l_end = null;
				CalendarEventMgr.getMonthStartNEnd(leave.getStartDateTime(), l_start, l_end);
				int l_count = entityManager.createNamedQuery("ApprovedLeave.getTotalLeavePerMonth", Integer.class)
						.setParameter("employeeID", l_emp.getEmployeeID())
						.setParameter("startDateTime", l_start)
						.setParameter("endDateTime", l_end)
						.setParameter("leaveTypeID", leave.getLeaveTypeID())
						.setParameter("status", HRContants.LeaveStatus.APPROVED)
						.getSingleResult();
				if(l_count >= l_leaveSchemeDetail.getDaysPerMonth())
					throw new NotEnoughLeaveBalanceException("Not enought leaves for this month.", NotEnoughLeaveBalanceException.MONTH);
			}
			
			List<Leave> l_PendingLeaves = entityManager.createNamedQuery("Leave.findOverlapped", Leave.class)
					.setParameter("employeeID", leave.getEmployeeID())
					.setParameter("startDateTime", leave.getStartDateTime())
					.setParameter("endDateTime", leave.getEndDateTime())
					.getResultList();
			if(l_PendingLeaves.size() > 0){
				if(l_PendingLeaves.size() == 1){
					if(leave.getLeaveID() != l_PendingLeaves.get(0).getLeaveID()){
						throw new ExistingLeaveException("There is a pending leave on " + leave.getStartDateTime() + ".");
					}
				}else{
					throw new ExistingLeaveException("There is a pending leave on " + leave.getStartDateTime() + ".");
				}
			}
			
			List<ApprovedLeave> l_ApprovedLeaves = entityManager.createNamedQuery("ApprovedLeave.findOverlapped", ApprovedLeave.class)
					.setParameter("employeeID", leave.getEmployeeID())
					.setParameter("startDateTime", leave.getStartDateTime())
					.setParameter("endDateTime", leave.getEndDateTime())
					.setParameter("status", HRContants.LeaveStatus.APPROVED)
					.getResultList();
			if(l_ApprovedLeaves.size() > 0)
				throw new ExistingLeaveException("There is a approved leave on " + leave.getStartDateTime() + ".");
			
			CalendarEvent l_calendarEvent = entityManager.find(CalendarEvent.class, leave.getEventID());
			l_calendarEvent.setDescription(leave.getReason());
			if((leave.getStartDateTime().getTime() + 3600000) < leave.getEndDateTime().getTime()){
				Calendar l_calendar = Calendar.getInstance();
				l_calendar.setTime(leave.getEndDateTime());
				l_calendar.add(Calendar.DAY_OF_MONTH, 1);
				
				l_calendarEvent.setEndDateTime(l_calendar.getTime());
			}
			l_calendarEvent.setEventName(leave.getReason());
			l_calendarEvent.setStartDateTime(leave.getStartDateTime());
			entityManager.merge(l_calendarEvent);
			
			if(leave.getLeaveTypeID() == 0){
				SystemProperty l_sysProp = entityManager.find(SystemProperty.class, "calendar_leave");
				if(l_sysProp == null)
					throw new DataNotFoundException("No default calendar leave.");
				leave.setLeaveTypeID(Long.parseLong(l_sysProp.getValue()));
				
			}
			entityManager.merge(leave);
			
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public void deleteLeave(long leaveId)throws Exception{
		openConnection();
		try {
			beginTransaction();
			
			Leave l_leave = entityManager.find(Leave.class, leaveId);
			CalendarEvent l_event = entityManager.find(CalendarEvent.class, l_leave.getEventID());
			
			entityManager.remove(l_leave);
			entityManager.remove(l_event);
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public void approvedLeaves(List<LeaveApprovedData> dataList, boolean isApproved, String loginId)throws Exception{
		openConnection();
		try {
			beginTransaction();
			Employee l_emp = entityManager.createNamedQuery("Employee.findByEmployeeNo", Employee.class)
					.setParameter("employeeNo", loginId)
					.getSingleResult();
		
			CalendarEvent l_calendarEvent = null;
			LeaveBalance l_leaveBalance;
			Calendar l_cal = Calendar.getInstance();
			for (LeaveApprovedData l_data : dataList) {
				Leave l_leave = entityManager.find(Leave.class, l_data.getLeaveId());
				l_cal.setTime(l_leave.getStartDateTime());
				l_leaveBalance = entityManager.createNamedQuery("LeaveBalance.findByEmployeeIdLeaveTypeYear", LeaveBalance.class)
						.setParameter("employeeID", l_leave.getEmployeeID())
						.setParameter("leaveTypeID", l_leave.getLeaveTypeID())
						.setParameter("year", l_cal.get(Calendar.YEAR))
						.getSingleResult(); 
				if(l_leaveBalance == null){
					
				}else{
					if((l_leaveBalance.getMaximum() - l_leaveBalance.getTotal()) < l_leave.getTotalDays()){
						
					}
					else{
						if(isApproved){
							l_leaveBalance.setTotal((int)(l_leaveBalance.getTotal() + l_leave.getTotalDays()));
							entityManager.merge(l_leaveBalance);
						}
						entityManager.createNamedQuery("CalendarEventParticipant.updateEditable")
						.setParameter("editable", false)
						.setParameter("eventID", l_leave.getEventID())
						.executeUpdate();
						
						l_leave.setApprovedBy(l_emp.getEmployeeID());
						Leave l_pendingLeave = entityManager.find(Leave.class, l_data.getLeaveId());
						entityManager.remove(l_pendingLeave);
						
						ApprovedLeave l_ApprovedLeave = new ApprovedLeave();
						l_ApprovedLeave.setApprovedBy(l_emp.getEmployeeID());
						l_ApprovedLeave.setApprovedDate(new Date());
						l_ApprovedLeave.setComment(l_data.getComment());
						l_ApprovedLeave.setCreatedDate(l_leave.getCreatedDate());
						l_ApprovedLeave.setCreatedBy(l_leave.getCreatedBy());
						l_ApprovedLeave.setReason(l_leave.getReason());
						l_ApprovedLeave.setEmployeeID(l_leave.getEmployeeID());
						l_ApprovedLeave.setEmployeeName(l_leave.getEmployeeName());
						l_ApprovedLeave.setEndDateTime(l_leave.getEndDateTime());
						l_ApprovedLeave.setReason(l_leave.getReason());
						l_ApprovedLeave.setEventID(l_leave.getEventID());
						l_ApprovedLeave.setLeaveDescription(l_leave.getLeaveDescription());
						l_ApprovedLeave.setLeaveID(l_leave.getLeaveID());
						l_ApprovedLeave.setLeaveTypeID(l_leave.getLeaveTypeID());
						l_ApprovedLeave.setStartDateTime(l_leave.getStartDateTime());
						l_ApprovedLeave.setStatus(isApproved ? HRContants.LeaveStatus.APPROVED : HRContants.LeaveStatus.REJECTED);
						l_ApprovedLeave.setTotalDays(l_leave.getTotalDays());
						l_ApprovedLeave.setLeavePeriodType(l_leave.getLeavePeriodType());
						entityManager.persist(l_ApprovedLeave);
						
						entityManager.remove(l_leave);
						
						l_calendarEvent = entityManager.find(CalendarEvent.class, l_leave.getEventID());
						l_calendarEvent.setEditable(false);
						entityManager.merge(l_calendarEvent);
						
						if(l_leave.getLeavePeriodType() != HRContants.LeavePeriodType.FULL){
							EmployeeAttendancePenalty l_empAttPenalty = null;
							try {
								l_empAttPenalty = entityManager.createNamedQuery("EmployeeAttendancePenalty.findByEmployeeIDInDatePenaltyType", EmployeeAttendancePenalty.class)
										.setParameter("employeeID", l_leave.getEmployeeID())
										.setParameter("datetime", l_leave.getStartDateTime())
										.setParameter("penaltyType", l_leave.getLeavePeriodType() == HRContants.LeavePeriodType.FIRST_HALF ? HRContants.AttendancePenaltyType.LATE_ARRIVAL : HRContants.AttendancePenaltyType.EARLY_DEPARTURE)
										.getSingleResult();
							} catch (NoResultException innerEx) {}
							if(l_empAttPenalty != null){
								l_empAttPenalty.setStatus(HRContants.AttendancePenaltyStatus.LEAVE);
								entityManager.merge(l_empAttPenalty);
							}
						}
						
						Employee l_appliedEmp = entityManager.find(Employee.class, l_leave.getEmployeeID());
						if(!l_appliedEmp.getEmail().equals(""))
						{
							SystemProperty l_userProp = entityManager.find(SystemProperty.class, "mail_user");
							SystemProperty l_pwdProp = entityManager.find(SystemProperty.class, "mail_password");
							if(l_userProp != null && l_pwdProp != null){
								try {
									String l_fileName = "";
									HashMap<String, String> l_parameters = new HashMap<String, String>();
									if(isApproved){
										l_fileName = CommonSingleton.ABSOLUTE_PATH + "data\\email_templates\\approved_leave.html";
										SimpleDateFormat l_dateTimeFormat = new SimpleDateFormat("hh:mm a d MMM yyyy");
										
										l_parameters.put("{name}", l_appliedEmp.getName());
										l_parameters.put("{event}", l_leave.getReason());
										l_parameters.put("{start}", l_dateTimeFormat.format(l_leave.getStartDateTime()));
									}
									else{
										l_fileName = CommonSingleton.ABSOLUTE_PATH + "data\\email_templates\\rejected_leave.html";
										SimpleDateFormat l_dateTimeFormat = new SimpleDateFormat("hh:mm a d MMM yyyy");
										l_parameters.put("{name}", l_appliedEmp.getName());
										l_parameters.put("{event}", l_leave.getReason());
										l_parameters.put("{start}", l_dateTimeFormat.format(l_leave.getStartDateTime()));
										l_parameters.put("{comment}", l_data.getComment());
									}
									
									EmailUtil.sendWithHTMLTemplate(l_appliedEmp.getEmail(), "Leave Approval for the event[" + l_leave.getReason() + "]", l_userProp.getValue(), l_pwdProp.getValue(), l_fileName, l_parameters);
								} catch (Exception e) {
									e.printStackTrace();
								}
							}
						}
					}
				}
			}
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	@SuppressWarnings("unchecked")
	public LeaveDataList getLeaves(LeaveCriteria cri, String empNo)throws Exception{
		openConnection();
		try {
			Employee l_emp = null;
			try {
				l_emp = entityManager.createNamedQuery("Employee.findByEmployeeNo", Employee.class)
					.setParameter("employeeNo", empNo)
					.getSingleResult();
			} catch (NoResultException e) {
				
			}
			if(l_emp != null){
				if(cri.isForApproval())
					cri.setSupervisorId(l_emp.getEmployeeID());
				else
					cri.setEmployeeId(l_emp.getEmployeeID());
				
				StoredProcedureQuery l_query = entityManager.createStoredProcedureQuery("sp_hr_Search_Leave", Leave.class);
				List<Leave> l_entityList = l_query.registerStoredProcedureParameter("keyword", String.class, ParameterMode.IN)
					.registerStoredProcedureParameter("pageIndex", Integer.class, ParameterMode.IN)
					.registerStoredProcedureParameter("pageSize", Integer.class, ParameterMode.IN)
					.registerStoredProcedureParameter("supervisorID", Long.class, ParameterMode.IN)
					.registerStoredProcedureParameter("employeeID", Long.class, ParameterMode.IN)
					.registerStoredProcedureParameter("leaveStatus", Byte.class, ParameterMode.IN)
					.registerStoredProcedureParameter("totalRecord", Long.class, ParameterMode.OUT)
					.setParameter("keyword", cri.getKeyword())
					.setParameter("pageIndex", cri.getIndex())
					.setParameter("pageSize", cri.getSize())
					.setParameter("supervisorID", cri.getSupervisorId())
					.setParameter("employeeID", cri.getEmployeeId())
					.setParameter("leaveStatus", cri.getLeaveStatus())
					.getResultList();
				
				LeaveDataList l_dataList = new LeaveDataList();
				l_dataList.setEntityList(l_entityList);
				l_dataList.setTotalRecord((long)l_query.getOutputParameterValue("totalRecord"));
				
	            return l_dataList;
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public Leave find(long id)throws Exception{
		openConnection();
		try {
			return entityManager.find(Leave.class, id);
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public Leave findLeaveByEventId(long eventId)throws Exception{
		openConnection();
		try {
			try {
				return entityManager.createNamedQuery("Leave.findByEventId", Leave.class)
						.setParameter("eventID", eventId)
						.getSingleResult();
			} catch (NoResultException innerEx) {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<LeaveBalance> getLeaveBalances(String employeeNo, int year)throws Exception{
		openConnection();
		try {
			Employee l_emp = null; 
			try {
				l_emp = entityManager.createNamedQuery("Employee.findByEmployeeNo", Employee.class)
					.setParameter("employeeNo", employeeNo)
					.getSingleResult();
			} catch (NoResultException e) {
				
			}
			if(l_emp != null){
				String l_query = "SELECT lb, lt FROM LeaveBalance lb, LeaveType lt WHERE lb.leaveTypeID = lt.leaveTypeID AND lb.employeeID = " + l_emp.getEmployeeID() + " AND lb.year = " + year ;
				List<Object[]> l_results = entityManager.createQuery(l_query).getResultList();
				List<LeaveBalance> l_leaveBalances = new ArrayList<LeaveBalance>();
				for (Object[] l_objArr : l_results) {
					LeaveBalance l_balance = (LeaveBalance)l_objArr[0];
					l_balance.setLeaveDescription(((LeaveType)l_objArr[1]).getName());
					l_leaveBalances.add(l_balance);
				}
				return l_leaveBalances;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
		return new ArrayList<LeaveBalance>();
	}
	
	public int getPendingLeave(String loginId)throws Exception{
		openConnection();
		try {
			Employee l_emp = null;
			try {
				l_emp = entityManager.createNamedQuery("Employee.findByEmployeeNo", Employee.class)
						.setParameter("employeeNo", loginId)
						.getSingleResult();
			} catch (NoResultException innerEx) {}
			if(l_emp==null)
				return 0;
			 return ((Number)entityManager.createNamedQuery("Leave.getLeaveCount")
				.setParameter("supervisorID", l_emp.getEmployeeID())
				.getSingleResult()).intValue();
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public List<LeaveTableData> getEmpLeavesForTable(LeaveTableCriteria cri, String loginId)throws Exception{
		openConnection();
		try {
			Employee l_loginedEmp = null;
			try {
				l_loginedEmp = entityManager.createNamedQuery("Employee.findByEmployeeNo", Employee.class)
						.setParameter("employeeNo", loginId)
						.getSingleResult();
			} catch (NoResultException innerEx) {}
			if(l_loginedEmp == null)
				return null;
			
			List<Employee> l_employees = null;
			if(cri.getSearchType() == 2)
			{
				l_employees = entityManager.createNamedQuery("Employee.findAllBySupervisor", Employee.class)
						.setParameter("supervisorID", l_loginedEmp.getEmployeeID())
						.setParameter("status", RecordStatus.delete)
						.getResultList();
			}
			else{
				if(cri.getKeyId() == 0){
					l_employees = entityManager.createNamedQuery("Employee.findAll", Employee.class)
							.setParameter("status", RecordStatus.delete)
							.getResultList();
				}else{
					l_employees = entityManager.createNamedQuery("Employee.findAllByDepartment", Employee.class)
							.setParameter("departmentID", cri.getKeyId())
							.setParameter("status", RecordStatus.delete)
							.getResultList();
				}
			}
			
			List<LeaveTableData> l_empLeavesList = new ArrayList<LeaveTableData>();
			
			for (Employee l_emp : l_employees) {
				List<ApprovedLeave> l_approvedLeaves = entityManager.createNamedQuery("ApprovedLeave.findBetween", ApprovedLeave.class)
						.setParameter("status", HRContants.LeaveStatus.APPROVED)
						.setParameter("startDateTime", cri.getStart())
						.setParameter("endDateTime", cri.getEnd())
						.setParameter("employeeID", l_emp.getEmployeeID())
						.getResultList();
				List<LeaveData> l_leaveDataList = new ArrayList<LeaveData>();
				for (ApprovedLeave l_leave : l_approvedLeaves) {
					LeaveData l_leaveData = new LeaveData();
					l_leaveData.setEnd(l_leave.getEndDateTime());
					l_leaveData.setStart(l_leave.getStartDateTime());
					l_leaveData.setLeaveTypeID(l_leave.getLeaveTypeID());
					l_leaveData.setTotal(l_leave.getTotalDays());
					l_leaveDataList.add(l_leaveData);
				}
				
				LeaveTableData l_empLeaves = new LeaveTableData();
				l_empLeaves.setEmployeeName(l_emp.getName());
				l_empLeaves.setLeaveDataList(l_leaveDataList);
				
				l_empLeavesList.add(l_empLeaves);
			}
			
			return l_empLeavesList;
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	@SuppressWarnings({ "unchecked" })
	public JSONArray getMobileLeaveList(byte leaveStatus, long leaveId, String loginId, int size, boolean myLeave)throws Exception{
		openConnection();
		try {
			String l_jpql = "select l.leaveID, l.employeeName, l.leaveDescription, l.startDateTime, l.endDateTime, l.totalDays, lt.color, l.reason, l.createdDate from ";
			String l_cri="";
			String l_entityName="";
			Employee l_emp = entityManager.createNamedQuery("Employee.findByEmployeeNo", Employee.class)
					.setParameter("employeeNo", loginId)
					.getSingleResult();
			if(myLeave){
				if(leaveStatus == HRContants.LeaveStatus.PENDING){
					l_entityName = "Leave";
					l_cri = "l.employeeID = " + l_emp.getEmployeeID();
					if(leaveId != 0)
						l_cri = " AND l.leaveID < " + leaveId;
					l_jpql += (l_entityName + " l, LeaveType lt WHERE l.leaveTypeID = lt.leaveTypeID AND " + l_cri);
				}else{
					l_entityName = "ApprovedLeave";
					l_cri = "l.employeeID = " + l_emp.getEmployeeID() + " AND l.status = " + leaveStatus;
					if(leaveId != 0)
						l_cri = " AND l.leaveID < " + leaveId;
					l_jpql += (l_entityName + " l, LeaveType lt WHERE l.leaveTypeID = lt.leaveTypeID AND " + l_cri);
				}
			}else{
				if(leaveStatus == HRContants.LeaveStatus.PENDING){
					l_entityName = "Leave";
					l_cri = " e.supervisor = " + l_emp.getEmployeeID();
					if(leaveId != 0)
						l_cri += " AND l.leaveID < " + leaveId;
					l_jpql += (l_entityName + " l, LeaveType lt, Employee e WHERE l.leaveTypeID = lt.leaveTypeID AND l.employeeID = e.employeeID AND " + l_cri);
				}else{
					l_entityName = "ApprovedLeave";
					l_cri = " l.approvedBy = " + l_emp.getEmployeeID() + " AND l.status = " + leaveStatus;
					if(leaveId != 0)
						l_cri += " AND l.leaveID < " + leaveId;
					l_jpql += (l_entityName + "  l, LeaveType lt WHERE l.leaveTypeID = lt.leaveTypeID AND " + l_cri);
				}
			}
			
			List<Object[]> l_resultList = entityManager.createQuery(l_jpql)
					.setFirstResult(0)
					.setMaxResults(size)
					.getResultList();
			
			SimpleDateFormat l_dateTimeFormat = new SimpleDateFormat("yyyyMMddHHmmss");
			JSONArray l_leaveDataList = new JSONArray();
			for (Object[] l_objArr : l_resultList) {
				JSONObject l_data = new JSONObject();
				l_data.put("leaveId", Long.parseLong(l_objArr[0].toString()));
				l_data.put("employeeName", l_objArr[1].toString());
				l_data.put("leaveDescription", l_objArr[2].toString());
				l_data.put("startDateTime", l_dateTimeFormat.format((Date)l_objArr[3]));
				l_data.put("endDateTime", l_dateTimeFormat.format((Date)l_objArr[4]));
				l_data.put("totalDays", Double.parseDouble(l_objArr[5].toString()));
				l_data.put("color", l_objArr[6].toString());
				l_data.put("subject", l_objArr[7].toString());
				l_data.put("createdDate", l_dateTimeFormat.format((Date)l_objArr[8]));
				l_leaveDataList.put(l_data);
			}
			return l_leaveDataList;
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
}
